package com.operation;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.mk.HibernateException;
import org.mk.type.MySQLTypeFactory;
import org.mk.type.Type;

import com.client.RecordCreateService;
import com.db.ColumnInfoBean;
import com.db.ForeignKey;
import com.db.connection.DBCPPostgresManager;
import com.db.domain.Communication;
import com.excel.read.Config;
import com.excel.read.DBTable;
import com.operation.model.OperationModel;
import com.service.ImportService;
import com.xml.Record;

/**
 * 数据操作镜像
 * 
 * @author Administrator
 * 
 */
public class OperationMap extends OperationModel {
	public static Log log = LogFactory.getLog(OperationMap.class);
	
	public List<String> tableNames;
	public Map<String, List<Record>> aMap;
	/** 待操作的数据库表名 */
	public String opTableName;
	/** 待操作的记录 */
	public Record record;
	
	

	public OperationMap(long storeFrom) {
		super(storeFrom);
	}

	public Record getRecord(String tableName, String PKValue) {
		Record aRecord = null;
		DBTable dbTable = (DBTable) Config.allTable
				.get(tableName.toLowerCase());

		List<Record> list = aMap.get(tableName.toLowerCase());
		for (Record rd : list) {
			String fieldValue = rd.fieldMap.get(
					dbTable.getPk().getName().toUpperCase()).toString();
			if (fieldValue.equals(PKValue)) {
				return rd;

			}
		}
		return aRecord;
	}

	/**
	 * 转成HTML
	 */
	public String getHTML() {
		StringBuffer sbf = new StringBuffer();
		sbf.append("<div num=\"" + tableNames.size() + "\">");
		for (String tableName : tableNames) {
			List<Record> list = aMap.get(tableName);
			if (list.size() > 0) {
				Record aRecord = list.get(0);
				sbf.append(aRecord.getHTML());
			} else {
				sbf.append("");
			}

		}
		sbf.append("</div>");
		return sbf.toString();

	}

	/**
	 * 判断主键对应的记录是否存在
	 * 
	 * @param opMap
	 * @param record
	 * @param conn
	 * @throws HibernateException
	 * @throws SQLException
	 *             May 29, 2009
	 */
	private boolean isConflict(Connection conn) throws HibernateException,
			SQLException {
		DBTable dbTable = (DBTable) Config.allTable.get(record.getTableName());
		Map request = record.fieldMap;
		ResultSet rs = null;
		String sql = dbTable.getSelectSQLByPKs();
		List<ColumnInfoBean> pks = dbTable.pks;
		PreparedStatement pstmt = null;
		try {
			// 执行SQL语句
			pstmt = conn.prepareStatement(sql);
			for (int i = 0; i < pks.size(); i++) {
				ColumnInfoBean cib = pks.get(i);
				Type type = MySQLTypeFactory
						.basic(cib.getNoPresitionTypeName());
				String fieldName = pks.get(i).getName().toLowerCase();
				// 判断是否外键
				Object value = null;
				Object fieldValue = request.get(fieldName.toUpperCase());
				value = type.getValue((String) fieldValue);
				if (value != null && value.equals("false")) {
					value = "N";
				} else if (value != null && value.equals("true")) {
					value = "Y";
				}
				type.nullSafeSet(pstmt, value, i + 1);

			}
			rs = pstmt.executeQuery();
			if (rs.next()) {
				return true;
			}
		} finally {
			DBCPPostgresManager.close(pstmt, rs);
		}
		return false;
	}

	@Override
	public void insert(Connection conn) throws HibernateException, SQLException {
		if (record.op.equalsIgnoreCase("insert")) {
			// 如果有主键冲突，则更新
			boolean isConflict = isConflict(conn);
			if (isConflict) {
				update(conn);
			} else {
				insertInner(conn);
			}

		} else if (this.record.op.equalsIgnoreCase("update")) {
			update(conn);
		}

	}

	public void insertInner(Connection conn) throws HibernateException,
			SQLException {
		DBTable dbTable = (DBTable) Config.allTable.get(record.getTableName());
		Map request = record.fieldMap;
		List<ColumnInfoBean> cinfoNameList = dbTable.getColumnInfos();
		List<String> selfNameList = new ArrayList<String>();
		for (ColumnInfoBean cinfo : cinfoNameList) {
			selfNameList.add(cinfo.getName());
		}
		String sql = dbTable.getInsertSQL();
		ColumnInfoBean pk = dbTable.getPk();
		PreparedStatement pstmt = null;
		try {
			// 执行SQL语句
			pstmt = conn.prepareStatement(sql);
			for (int i = 0; i < cinfoNameList.size(); i++) {
				ColumnInfoBean cib = cinfoNameList.get(i);

				Type type = MySQLTypeFactory
						.basic(cib.getNoPresitionTypeName());
				String fieldName = selfNameList.get(i).toLowerCase();
				// 判断是否外键
				Object value = null;

				Object fieldValue = request.get(fieldName.toUpperCase());
				if (fieldValue != null && fieldValue.equals("40.00")) {
					System.out.println(99);
				}
				value = type.getValue((String) fieldValue);
				if (value != null && value.equals("false")) {
					value = "N";
				} else if (value != null && value.equals("true")) {
					value = "Y";
				}
				type.nullSafeSet(pstmt, value, i + 1);

			}
			pstmt.executeUpdate();

		} finally {
			DBCPPostgresManager.close(pstmt, null);
		}
	}

	
	/**
	 * 
	 * @param opMap
	 * @param record
	 *            含有待修改的列
	 * @throws SQLException
	 */
	public  void update(Connection conn) throws SQLException {
		DBTable dbTable = (DBTable) Config.allTable.get(record.getTableName());
		Map request = record.fieldMap;
		List<ColumnInfoBean> cinfoNoPKNameList = record.getNoPKColumnInfos();
		List<ColumnInfoBean> cinfoPKNameList = record.getPKColumnInfos();
		List<String> selfNameList = new ArrayList<String>();
		for (ColumnInfoBean cinfo : cinfoNoPKNameList) {
			selfNameList.add(cinfo.getName());
		}
		String sql = record.getUpdateSQL();
		ColumnInfoBean pk = dbTable.getPk();
		PreparedStatement pstmt = null;
		try {
			// 执行SQL语句
			pstmt = conn.prepareStatement(sql);
			
			// 执行SQL语句
			pstmt = conn.prepareStatement(sql);
			int i = 0;
			for (i = 0; i < cinfoNoPKNameList.size(); i++) {
				ColumnInfoBean cib = cinfoNoPKNameList.get(i);
				Type type = MySQLTypeFactory
						.basic(cib.getNoPresitionTypeName());
				String fieldName = selfNameList.get(i).toLowerCase();
				// 判断是否外键
				Object value = null;
				Object fieldValue = request.get(fieldName.toUpperCase());
				value = type.getValue((String) fieldValue);
				if (value != null && value.equals("false")) {
					value = "N";
				} else if (value != null && value.equals("true")) {
					value = "Y";
				}
				type.nullSafeSet(pstmt, value, i + 1);

			}

			// 主键
			for (int j = 0; j < cinfoPKNameList.size(); j++) {
				ColumnInfoBean cib = cinfoPKNameList.get(j);
				Type type = MySQLTypeFactory
						.basic(cib.getNoPresitionTypeName());
				String fieldName = cib.getName().toLowerCase();
				Object fieldValue = request.get(fieldName.toUpperCase());
				Object value = type.getValue((String) fieldValue);
				type.nullSafeSet(pstmt, value, i + (j + 1));
			}
			pstmt.executeUpdate();

		} finally {
			DBCPPostgresManager.close(pstmt, null);
		}
	}
	
	
	public String getRecordText(Connection conn) throws HibernateException, SQLException {
		DBTable dbTable = (DBTable) Config.allTable.get(record.getTableName());
		List<ColumnInfoBean> cinfoNameList = dbTable.getColumnInfos();
		List<String> selfNameList = new ArrayList<String>();
		for (ColumnInfoBean cinfo : cinfoNameList) {
			selfNameList.add(cinfo.getName());
		}
		String sql = dbTable.getSelectSQL();
		ColumnInfoBean pk = dbTable.getPk();
		// 执行SQL语句
		PreparedStatement pstmt = conn.prepareStatement(sql);
		Type atype = MySQLTypeFactory.basic(pk.getNoPresitionTypeName());
		Object value = record.fieldMap.get(pk.getName().toUpperCase());
		atype.nullSafeSet(pstmt, value, 1);
		ResultSet rs = pstmt.executeQuery();
		String xml = "";
		if (rs.next()) {
			xml = POXML.getXML(dbTable, rs);
		}
		return xml;
	}
	
	
	
	
	/**
	 * @功能 根据记录查找对应的外键记录是否存在于本地数据库中，如果 不存在添加新的请求
	 * @param opMap
	 * @param recordId
	 * @return 该记录的外键请求，这些外键对应的记录在本地是不存在
	 * @throws SQLException
	 *             May 23, 2009
	 */
	public  List<Communication> getRequestInit(Communication parent) throws SQLException {
		boolean returnFlag = true;
		List<Communication> aList = new ArrayList<Communication>();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ResultSet res = null;
		try {
			conn = DBCPPostgresManager.getConnection();
			// 执行SQL语句
			DBTable dbTable = (DBTable) Config.allTable.get(opTableName
					.toLowerCase());
			Map request = record.fieldMap;
			List<ColumnInfoBean> cinfoNameList = dbTable.getColumnInfos();
			List<String> selfNameList = new ArrayList<String>();
			for (ColumnInfoBean cinfo : cinfoNameList) {
				selfNameList.add(cinfo.getName());
			}
			ColumnInfoBean pk = dbTable.getPk();
			// 执行SQL语句

			for (int i = 0; i < cinfoNameList.size(); i++) {
				ColumnInfoBean cib = cinfoNameList.get(i);
				Type type = MySQLTypeFactory
						.basic(cib.getNoPresitionTypeName());
				String fieldName = selfNameList.get(i).toLowerCase();
				boolean isFK = dbTable.isFK(fieldName);
				if (isFK) {
					Object value = null;
					Object fieldValue = request.get(fieldName.toUpperCase());
					if (fieldValue == null) {
						value = null;
						continue;
					} else {
						value = type.getValue((String) fieldValue);
					}

					ForeignKey aForeignKey = dbTable.getFK(fieldName
							.toUpperCase());
					String toTableName = aForeignKey.getToTableName();

					String toFieldname = aForeignKey.getToField();
					String sql = "select * from " + toTableName + " where "
							+ toFieldname + "=?";
					pstmt = conn.prepareStatement(sql);
					type.nullSafeSet(pstmt, value, 1);
					rs = pstmt.executeQuery();
					if (rs.next()) {
						rs.close();
						continue;
					} else {
						Communication aCommunication = new Communication();
						returnFlag = false;
						// 添加请求

						String xml = POXML.getRecord(toTableName, toFieldname,
								(Long) ((Integer) value).longValue());
						aCommunication.request = xml;
						aCommunication.clientFrom = RecordCreateService.orgid;
						// 来自哪里，就发到哪里
						aCommunication.clientTo = record.storeFrom;
						aCommunication.flag = "N";
						
							aCommunication.parent = 0;
							aCommunication.level = 1;
							// aCommunication.recordId = parent.recordId;
						 

						aList.add(aCommunication);
					}
				}

			}

		} catch (Exception ex) {
			log.error("ErrorImport : " + ex.toString());
		} finally {
			DBCPPostgresManager.close(conn, pstmt, rs);

		}

		return aList;
	}
	
	
	public  List<Communication> getRequest(Communication parent) throws SQLException {
		boolean returnFlag = true;
		List<Communication> aList = new ArrayList<Communication>();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ResultSet res = null;
		try {
			conn = DBCPPostgresManager.getConnection();
			// 执行SQL语句
			DBTable dbTable = (DBTable) Config.allTable.get(opTableName
					.toLowerCase());
			Map request = record.fieldMap;
			List<ColumnInfoBean> cinfoNameList = dbTable.getColumnInfos();
			List<String> selfNameList = new ArrayList<String>();
			for (ColumnInfoBean cinfo : cinfoNameList) {
				selfNameList.add(cinfo.getName());
			}		
			// 执行SQL语句

			for (int i = 0; i < cinfoNameList.size(); i++) {
				ColumnInfoBean cib = cinfoNameList.get(i);
				Type type = MySQLTypeFactory.basic(cib.getNoPresitionTypeName());
				String fieldName = selfNameList.get(i).toLowerCase();
				boolean isFK = dbTable.isFK(fieldName);
				Object value = null;																
				if (isFK) {					
					Object fieldValue = request.get(fieldName.toUpperCase());
					if (fieldValue == null) {
						value = null;
						continue;
					} else {
						value = type.getValue((String) fieldValue);
					}

					ForeignKey aForeignKey = dbTable.getFK(fieldName
							.toUpperCase());
					String toTableName = aForeignKey.getToTableName();
					String toFieldname = aForeignKey.getToField();
					String sql = "select * from " + toTableName + " where "
							+ toFieldname + "=?";
					pstmt = conn.prepareStatement(sql);
					type.nullSafeSet(pstmt, value, 1);
					String tableAndID = toTableName+"_"+value;
					TreeTool aTreeTool = parent.getTreeTool();
					Map idValueMap = aTreeTool.getNameID();
				
	
					rs = pstmt.executeQuery();
					if (rs.next()) {
						rs.close();
						continue;
					} else if(idValueMap.get(tableAndID) != null) {
						Communication tempCommnuication = (Communication)idValueMap.get(tableAndID);
						if(tempCommnuication.parentNode == parent) {
							continue;
						}else {
							//跟新外键字段为空					
							record.fieldMap.put(fieldName.toUpperCase(), null);
							parent.getTreeTool().addForeignSQL("update "+dbTable.getName()+" set fieldName = "+fieldValue);							
							//value
							//加外键更新缓存
						}
					}else{					
						Communication aCommunication = new Communication();
						returnFlag = false;
						// 添加请求

						String xml = POXML.getRecord(toTableName, toFieldname,
								(Long) ((Integer) value).longValue());
						aCommunication.request = xml;
						aCommunication.clientFrom = RecordCreateService.orgid;
						// 来自哪里，就发到哪里
						aCommunication.clientTo = record.storeFrom;
						aCommunication.flag = "N";
						if (parent == null) {
							aCommunication.parent = 0;
							aCommunication.level = 1;
							// aCommunication.recordId = parent.recordId;
						} else {
							aCommunication.parent = parent.id;
							aCommunication.level = parent.level + 1;
							aCommunication.recordId = parent.recordId;
						}

						aList.add(aCommunication);
					}
				}

			}

		} catch (Exception ex) {
			log.error("ErrorImport : " + ex.toString());
		} finally {
			DBCPPostgresManager.close(conn, pstmt, rs);

		}

		return aList;
	}
	
	
	public  void execute(Connection conn) throws SQLException{}
	
}
